-- Purge all archived versions of a specified document document  
CREATE PROCEDURE [dbo].[asi_DocumentPurgeArchivedVersions]
    @documentVersionKey uniqueidentifier,
    @allDocuments bit = 0
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @documentsToDelete TABLE (DocumentKey uniqueidentifier,
                                      DocumentVersionKey uniqueidentifier,
                                      AccessKey uniqueidentifier,
                                      PublishedVersionExists bit)
    INSERT INTO @documentsToDelete
    SELECT d.DocumentKey, d.DocumentVersionKey, d.AccessKey, 0
      FROM DocumentMain d
    WHERE DocumentStatusCode IN (50, 80)
           AND NOT EXISTS (SELECT 1 FROM DocumentTypeRef WHERE PublishWorkflowDocumentKey = d.DocumentKey)
           AND NOT EXISTS (SELECT 1 FROM DocumentTypeRef WHERE RecycleWorkflowDocumentKey = d.DocumentKey)
           AND NOT EXISTS (SELECT 1 FROM GroupTypeRef WHERE MemberQueryFolderKey = d.DocumentKey)
           AND NOT EXISTS (SELECT 1 FROM PackageQueue WHERE QueueReleaseWorkflowKey = d.DocumentKey)
           AND NOT EXISTS (SELECT 1 FROM RFMDefinition WHERE TransactionDocumentKey = d.DocumentKey)
           AND NOT EXISTS (SELECT 1 FROM RFMDefinition WHERE UserDocumentKey = d.DocumentKey)
           AND NOT EXISTS (SELECT 1 FROM WorkItem WHERE ProcessEngineKey = d.DocumentKey)
           AND NOT EXISTS (SELECT 1 FROM WorkItem WHERE WorkflowDefinitionKey = d.DocumentKey)
    AND (@allDocuments = 1 OR d.DocumentVersionKey = @documentVersionKey)

    UPDATE d
       SET PublishedVersionExists = 1
      FROM @documentsToDelete d
    WHERE EXISTS (SELECT 1 FROM DocumentMain x WHERE d.DocumentVersionKey = x.DocumentVersionKey AND x.DocumentStatusCode = 40)

    -- Delete unpublished documents from the PublishRequest and PublishRequestDetail tables
    DELETE p
      FROM PublishRequest p
           INNER JOIN @documentsToDelete dtd ON p.DocumentKey = dtd.DocumentKey
    DELETE p
      FROM PublishRequestDetail p
           INNER JOIN @documentsToDelete dtd ON p.ContentDocumentKey = dtd.DocumentKey

    -- Delete the unpublished documents
    DELETE d
      FROM [dbo].[DocumentMain] d
           INNER JOIN @documentsToDelete dtd ON d.DocumentKey = dtd.DocumentKey
           
    -- Delete any unused hierarchy and access keys
    DELETE h
      FROM Hierarchy h
           INNER JOIN @documentsToDelete dtd ON h.UniformKey = dtd.DocumentVersionKey AND h.UniformType = 'DocumentVersion'
    WHERE h.UniformKey NOT IN (SELECT DocumentVersionKey FROM DocumentMain)
           AND h.HierarchyKey NOT IN (SELECT ParentHierarchyKey FROM Hierarchy)
    DELETE ai
      FROM [dbo].[AccessItem] ai
           INNER JOIN [dbo].[AccessMain] am ON ai.AccessKey = am.AccessKey
           INNER JOIN @documentsToDelete dtd ON ai.AccessKey = dtd.AccessKey
     WHERE dtd.PublishedVersionExists = 0 AND am.AccessScope = 'Local'
    DELETE am
      FROM [dbo].[AccessMain] am
           INNER JOIN @documentsToDelete dtd ON am.AccessKey = dtd.AccessKey
     WHERE dtd.PublishedVersionExists = 0 AND am.AccessScope = 'Local'
END

GO